package com.example.androidapp2;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import com.example.androidapp2.DbClasses.TotalCategoria;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;

public class DbAdapter extends SQLiteOpenHelper {
	private static String DB_NAME = "DrNomy";
	private static String DB_PATH = "/DroidNomy/";
	private SQLiteDatabase myDataBase; 
	private final Context myContext;

	public DbAdapter(Context context) {
		super(context, DB_NAME, null, 1);
		myContext = context;
	}


	public void createDataBase() throws IOException{

		boolean dbExist = checkDataBase();

		if(dbExist){
			//do nothing - database already exist
		}else{

			//By calling this method and empty database will be created into the default system path
			//of your application so we are gonna be able to overwrite that database with our database.
			this.getWritableDatabase();

			try {

				copyDataBase();

			} catch (IOException e) {

				throw new Error("Error copying database");

			}
		}

	}

	/**
	 * Check if the database already exist to avoid re-copying the file each time you open the application.
	 * @return true if it exists, false if it doesn't
	 */
	private boolean checkDataBase(){

		SQLiteDatabase checkDB = null;

		try{
			if (Environment.getExternalStorageState().equals("mounted")) {
				String sdCard = Environment.getExternalStorageDirectory().getPath();

				String myPath = sdCard + DB_PATH + DB_NAME;
				checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
			}
		}catch(SQLiteException e){
			throw e;
		}

		if(checkDB != null){
			checkDB.close();
		}

		return checkDB != null ? true : false;
	}

	private void copyDataBase() throws IOException{
		if (Environment.getExternalStorageState().equals("mounted")) {
			String sdCard = Environment.getExternalStorageDirectory().getPath();

			//Open your local db as the input stream
			InputStream myInput = myContext.getAssets().open(DB_NAME);

			// Path to the just created empty db
			String outFileName = sdCard + DB_PATH + DB_NAME;

			//Open the empty db as the output stream
			OutputStream myOutput = new FileOutputStream(outFileName);

			//transfer bytes from the inputfile to the outputfile
			byte[] buffer = new byte[1024];
			int length;
			while ((length = myInput.read(buffer))>0){
				myOutput.write(buffer, 0, length);
			}

			//Close the streams
			myOutput.flush();
			myOutput.close();
			myInput.close();
		}
	}

	public void openDataBase() throws SQLException{

		if (Environment.getExternalStorageState().equals("mounted")) {
			String sdCard = Environment.getExternalStorageDirectory().getPath();

			String myPath = sdCard + DB_PATH + DB_NAME;

			myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);			
		}
	}

	@Override
	public synchronized void close() {

		if(myDataBase != null)
			myDataBase.close();

		super.close();

	}

	public int GetTotalMovimientos(String tipo)
	{				
		Cursor c = this.myDataBase.query("Movimientos", new String[]{"SUM(Valor) AS Total"},"tipo='"+tipo+"' AND Estado = 1" , null, null, null, null);
		c.moveToFirst();

		return c.getInt(c.getColumnIndex("Total"));
	}

	public List<TotalCategoria> GetTotalesPorCategoria()
	{
		String sql = "SELECT D.Valor AS Desc,SUM(M.Valor) AS Total "+
				"FROM Movimientos M JOIN DataRef D "+
				"ON  M.Categoria=D.Codigo "+
				"WHERE D.ControlNm='spnCategorias' "+
				"AND M.Tipo='Eg' "+
				"GROUP BY M.Categoria";

		List<TotalCategoria> ls = new ArrayList<TotalCategoria>();
		Cursor c = this.myDataBase.rawQuery(sql,null);

		c.moveToFirst();
		ls.add(new TotalCategoria(c.getString(0),c.getInt(1)));
		while(!c.isLast()) {
			c.moveToNext();
			ls.add(new TotalCategoria(c.getString(0),c.getInt(1)));
		}

		return ls;
	}

	public void InsertMovimiento(int valor, String tipo, String fecha, String medio, String cate)
	{		
		ContentValues values = new ContentValues();
		values.put("Valor", valor);
		values.put("Tipo", tipo);
		values.put("Fecha", fecha);
		values.put("Medio", medio);
		values.put("Categoria", cate);
		values.put("Estado", 1);

		this.myDataBase.insert("Movimientos", null, values);
	}	
	
	public void UpdateMovimientosAInactivo(String condicion)
	{
		String sql = "UPDATE Movimientos "+
					 "SET Estado = 0 "+
					 "WHERE IdMovimiento in "+condicion;

				this.myDataBase.execSQL(sql);		
	}
	
	public void InactivarUltimo()
	{
		UpdateMovimientosAInactivo("(SELECT Max(IdMovimiento) " +
								   "FROM Movimientos " +
								   "WHERE Estado = 1 " +
								   "AND Tipo = 'Eg')");
	}
	
	public void InactivarMes()
	{
		UpdateMovimientosAInactivo("(SELECT IdMovimiento FROM Movimientos WHERE )");
	}
	

	public List<String> GetCategorias(String nombre)
	{			
		List<String> ls = new ArrayList<String>();
		Cursor c = this.myDataBase.query("DataRef", new String[]{"Valor"},"ControlNm ='"+nombre+"'" , null, null, null, "Codigo");

		c.moveToFirst();
		ls.add(c.getString(0));
		while(!c.isLast()) {
			c.moveToNext();
			ls.add(c.getString(0));
		}

		return ls;
	}

	@Override
	public void onCreate(SQLiteDatabase arg0) {
		// TODO Auto-generated method stub

	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub

	}

}
